package com.zhenwgang.listen_books.repository;

import com.zhenwgang.listen_books.data_object.AlbumDO;
import com.zhenwgang.listen_books.util.DBUtil;
import com.zhenwgang.listen_books.util.Log;
import com.zhenwgang.listen_books.view_object.AlbumView;
import com.zhenwgang.listen_books.view_object.ResultView;
import lombok.SneakyThrows;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Violet
 * @date 2022/07/12 20:04
 **/
public class AlbumRepo {
    @SneakyThrows
    public void selectBySameUid(ResultView resultView){
        try (Connection c = DBUtil.connection()) {
            String sql = "select aid, username, title, cover from albums a join users u on a.uid = u.uid where state = 2 order by aid desc";
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        int aid = rs.getInt("aid");
                        String username = rs.getString("username");
                        String title = rs.getString("title");
                        String cover = rs.getString("cover");

                        AlbumView view = new AlbumView(aid, username, title, cover);
                        Log.println(view);
                        resultView.albumList.add(view);
                    }
                }
            }
        }
    }

    @SneakyThrows
    public Integer selectUidByAid(int aid){
        String sql = "select uid from albums where aid = ?";
        try (Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1, aid);
                Log.println("执行的SQL为：" + ps);

                try (ResultSet rs = ps.executeQuery()){
                    if (!rs.next()){
                        return null;
                    }
                    return rs.getInt(1);
                }
            }
        }
    }

    @SneakyThrows
    public void updatePublish(int aid){
        String sql = "update albums set state = 2 where aid = ?";
        try (Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1, aid);
                Log.println("执行的SQL为：" + ps);
                ps.executeUpdate();
            }
        }
    }

    @SneakyThrows
    public void updateWithdraw(int aid){
        String sql = "update albums set state = 0 where aid = ?";
        try (Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1, aid);
                Log.println("执行的SQl为：" + ps);
                ps.executeUpdate();
            }
        }
    }

    @SneakyThrows
    public List<AlbumDO> selectListByUid(int uid){
        List<AlbumDO> list = new ArrayList<>();
        String sql = "select aid, uid, title, cover, state from albums where uid = ?";
        try (Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1, uid);
                Log.println("执行的SQL为：" + ps);

                try (ResultSet rs = ps.executeQuery()){
                    while (rs.next()){
                        list.add(new AlbumDO(
                                rs.getInt("aid"),
                                rs.getInt("uid"),
                                rs.getString("title"),
                                rs.getString("cover"),
                                rs.getInt("state")
                        ));
                    }
                }
            }
        }
        return list;
    }

    @SneakyThrows
    public void insert(int uid, String title, String cover, int state){
        String sql = "insert into albums (uid, title, cover, state) values (?, ?, ?, ?)";
        try (Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1, uid);
                ps.setString(2, title);
                ps.setString(3, cover);
                ps.setInt(4, state);

                Log.println("执行的SQL为：" + ps);
                ps.executeUpdate();
            }
        }
    }

    @SneakyThrows
    public AlbumDO selectOneByUidAndAid(int uid, int aid) {
        String sql = "select aid, uid, title, cover, state from albums where uid = ? and aid = ?";
        try (Connection c = DBUtil.connection()) {
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setInt(1, uid);
                ps.setInt(2, aid);

                Log.println("执行的SQL为：" + ps);

                try (ResultSet rs = ps.executeQuery()) {
                    rs.next();
                    return new AlbumDO(
                            rs.getInt("aid"),
                            rs.getInt("uid"),
                            rs.getString("title"),
                            rs.getString("cover"),
                            rs.getInt("state")
                    );
                }
            }
        }
    }
}